package Control;
/**
 *
 * @author zhaoyingxi
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.User;
import util.JdbcUtil;
public class UserControl {
	public static void delete(User user)
	{
		Connection connection = JdbcUtil.getConnection();
		String sql = "delete FROM usertable where loginname=?";
		PreparedStatement statement = null;
		try {
				connection.setAutoCommit(false);
				statement = connection.prepareStatement(sql);
				statement.setString(1, user.getLoginName());
				statement.executeUpdate();
				connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				//Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally{
			JdbcUtil.closeConnection();
		}
	}
	public static java.util.List<User> find(){
		Connection connection = JdbcUtil.getConnection();
		java.util.List<User> list = new ArrayList<User>();
		String sql = "select loginname,password,username,phone,email from usertable where status='2'";
		PreparedStatement statement = null;
		try {
				connection.setAutoCommit(false);
				statement = connection.prepareStatement(sql);
				ResultSet resultSet = statement.executeQuery();
				while(resultSet.next()) {
					User user = new User();
					user.setEmail(resultSet.getString("email"));
					user.setLoginName(resultSet.getString("loginname"));
					user.setPassWord(resultSet.getString("password"));
					user.setPhone(resultSet.getString("phone"));
					user.setUserName(resultSet.getString("username"));
					list.add(user);
				}
				connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				//Auto-generated catch block
				e1.printStackTrace();
				return null;
			}
			e.printStackTrace();
		}
		finally{
			JdbcUtil.closeConnection();
		}
		return list;
	}
	public static User update(User user)
	{
		Connection connection = JdbcUtil.getConnection();
		String sql = "update usertable set password=?,username=?,phone=?,email=? where loginname=?";
		PreparedStatement statement = null;
		try {
				connection.setAutoCommit(false);
				statement = connection.prepareStatement(sql);
				statement.setString(1, user.getPassWord());
				statement.setString(2, user.getUserName());
				statement.setString(3, user.getPhone());
				statement.setString(4, user.getEmail());
				statement.setString(5, user.getLoginName());
				statement.executeUpdate();
				connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
				return null;
			}
			e.printStackTrace();
		}
		finally{
			JdbcUtil.closeConnection();
		}
		return user;
	}
	public static User login(String loginName,String passWord){
		User user = new User();
		Connection connection = JdbcUtil.getConnection();
		String sql = "SELECT * FROM usertable WHERE loginname=? AND password=?";
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, loginName);
			statement.setString(2, passWord);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				user.setId(resultSet.getInt("id"));
				user.setEmail(resultSet.getString("email"));
				user.setLoginName(resultSet.getString("loginname"));
				user.setPassWord(resultSet.getString("password"));
				user.setUserName(resultSet.getString("username"));
				user.setStatus(resultSet.getString("status"));
			}
		} catch (SQLException e) {
			//Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		finally{
			JdbcUtil.closeConnection();
		}
		return user;
	}
	public static User findByLoginName(String loginName){
		User user = new User();
		Connection connection = JdbcUtil.getConnection();
		String sql = "SELECT * FROM usertable WHERE loginname=?";
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(sql);
			statement.setString(1, loginName);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				user.setId(resultSet.getInt("id"));
				user.setEmail(resultSet.getString("email"));
				user.setLoginName(resultSet.getString("loginname"));
				user.setPassWord(resultSet.getString("password"));
				user.setUserName(resultSet.getString("username"));
			}
		} catch (SQLException e) {
			//Auto-generated catch block
			e.printStackTrace();
			return null;
		}
		finally{
			JdbcUtil.closeConnection();
		}
		return user;
	}
	public static boolean register(User user){
		boolean result = false;
		Connection connection = JdbcUtil.getConnection();
		String sql = "INSERT INTO usertable(loginname,password,username,phone,email,status) values (?,?,?,?,?,?) ";
		PreparedStatement statement = null;
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(sql);
			statement.setString(1, user.getLoginName());
			statement.setString(2, user.getPassWord());
			statement.setString(3, user.getUserName());
			statement.setString(4, user.getPhone());
			statement.setString(5, user.getEmail());
			statement.setString(6, user.getStatus());
			statement.executeUpdate();
			connection.commit();
			result=true;
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally{
			JdbcUtil.closeConnection();
		}
		return result;
	}
	public static void main(String[] args) {

            //main()
	}
}
